package achievement.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import db.dbConnection;
import achievement.bean.Achievement;

public class AchievementService {
  dbConnection db=new dbConnection();
  
  public List ShowAllAchievement(int pagenow,int pagesize){
	  List<Achievement> list=new ArrayList();
	  String sql="select * from project where sulfur=0 limit ?,?";
	  Connection conn=db.getConnection();  
	  try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow*pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				
				Achievement achievement=new Achievement();
				
				 String item=rs.getString("item");
				 String method=rs.getString("method");
				 String projectname=rs.getString("projectname");
				 String shape=rs.getString("shape");
				 String size=rs.getString("size");
				 String technology=rs.getString("technology");
				 
				 item=item.replace("m³", "m&sup3;");
				 method=method.replace("m³", "m&sup3;");
				 projectname=projectname.replace("m³", "m&sup3;");
				 shape=shape.replace("m³", "m&sup3;");
				 size= size.replace("m³", "m&sup3;");
				 technology=technology.replace("m³", "m&sup3;");
				 
				achievement.setId(rs.getInt("id"));
				achievement.setItem(item);
				achievement.setMethod(method);
				achievement.setProjectname(projectname);
				achievement.setShape(shape);
				achievement.setSize(size);
				achievement.setTechnology(technology);
				achievement.setTime(rs.getString("time"));
				list.add(achievement);
			}		
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
	  return list;
  }
  public int CountAllAchievement(){
	  int count=0;
	  String sqlString="select count(*) from project where sulfur=0 ";
	 Connection conn=db.getConnection();
	 try{
		 PreparedStatement past=conn.prepareStatement(sqlString);
		 ResultSet rSet=past.executeQuery();
		 while(rSet.next()){
			 count=rSet.getInt(1);
		 }
	 }catch(Exception e){
		 e.printStackTrace();
	 }
	  return count; 	
  }
  public boolean AddAchievement(Achievement achievement){
	 String item=achievement.getItem();
	 String projectname=achievement.getProjectname();
	 String size=achievement.getSize();
	 String time=achievement.getTime();
	 String method=achievement.getMethod();
	 String technology=achievement.getTechnology();
	 String shape=achievement.getShape();
	 
	 item=item.replace("m³", "m&sup3;");
	 projectname=projectname.replace("m³", "m&sup3;");
	 size=size.replace("m³", "m&sup3;");
	 technology=technology.replace("m³", "m&sup3;");
	 method=method.replace("m³", "m&sup3;");
	 shape=shape.replace("m³", "m&sup3;");
	 
	  boolean flag = false;
	  Connection conn = db.getConnection();
		try {
			PreparedStatement past = null;
			String sql = "insert into project(item,projectname,size,time,method,technology,shape,sulfur) values ("
					+ "'" + item
					+ "'" + "," + "'" + projectname + "'" + ","+"'"+ size + "'"+ "," + "'" + time + "'"+ "," + "'" + method
					+ "'"+ "," + "'" + technology + "'"+ "," + "'" + shape + "'"+",0)";
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			System.out.print(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
	
		return flag;
  }
  
  public Achievement ShowoneAchievement(Achievement achievementbean){
	  Achievement achievement=new Achievement();
	  String sql="select * from project where id=?";
			  Connection conn=db.getConnection();
	  		  PreparedStatement past=null;
	  		try {
				past = conn.prepareStatement(sql);
				past.setInt(1, achievementbean.getId());
				ResultSet rs = past.executeQuery();
				if (!rs.next()) {
					return null;
				}
				 String item=rs.getString("item");
				 String method=rs.getString("method");
				 String projectname=rs.getString("projectname");
				 String shape=rs.getString("shape");
				 String size=rs.getString("size");
				 String technology=rs.getString("technology");
				 
				 item=item.replace("m³", "m&sup3;");
				 method=method.replace("m³", "m&sup3;");
				 projectname=projectname.replace("m³", "m&sup3;");
				 shape=shape.replace("m³", "m&sup3;");
				 size= size.replace("m³", "m&sup3;");
				 technology=technology.replace("m³", "m&sup3;");
				 
				achievement.setId(rs.getInt("id"));
				achievement.setItem(item);
				achievement.setMethod(method);
				achievement.setSize(size);
				achievement.setProjectname(projectname);
				achievement.setTime(rs.getString("time"));
				achievement.setShape(shape);
				achievement.setTechnology(technology);
				rs.close();
				past.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return achievement;
		}
	  public boolean DeleteAchievement(Achievement achievementbean){
		  boolean flag = false;
			Connection conn = db.getConnection();
			String sql1 = "delete from project where id =" + "'"
					+ achievementbean.getId() + "'";
			
			try {
				PreparedStatement past = null;
				past = conn.prepareStatement(sql1);
				past.executeUpdate(sql1);
				flag = true;
				past.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return flag;
	  }
	  public boolean ModifyAchievement(Achievement achievementbean){
		  boolean flag = false;
			String sql = "update project set item=?,projectname=?,size=?,time=?,method=?,technology=?,shape=? where id=?";
			Connection conn = db.getConnection();
			PreparedStatement past = null;
			try {
				past = conn.prepareStatement(sql);
				
				String item=achievementbean.getItem();
				 String projectname=achievementbean.getProjectname();
				 String size=achievementbean.getSize();
				 String method=achievementbean.getMethod();
				 String technology=achievementbean.getTechnology();
				 String shape=achievementbean.getShape();
				 
				 item=item.replace("m³", "m&sup3;");
				 projectname=projectname.replace("m³", "m&sup3;");
				 size=size.replace("m³", "m&sup3;");
				 technology=technology.replace("m³", "m&sup3;");
				 method=method.replace("m³", "m&sup3;");
				 shape=shape.replace("m³", "m&sup3;");
				 
				past.setString(1,item);
				past.setString(2,projectname);
				past.setString(3, size);
				past.setString(4, achievementbean.getTime());
				past.setString(5, method);
				past.setString(6, technology);
				past.setString(7, shape);
				past.setInt(8, achievementbean.getId());
		
				past.executeUpdate();
				past.close();
				flag = true;
				   System.out.print(achievementbean.getId());
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return flag;
	  }
	  
  }

